﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BangumiWpfApp1
{
    public class SubjectDao
    {
        public void add(Subject subject)
        {
            using (IDbConnection conn = DapperService.MySqlConnection())
            {
                int id = subject.id;
                int count = conn.QuerySingleOrDefault<int>("SELECT COUNT(id) from `subject` WHERE id = " + id);
                if (count < 1)
                {
                    string sqlCommandStr = @"insert into subject(id,url,type,name,name_cn,summary,eps,eps_count,air_date,air_weekday,score,`rank`,rate_total) values(@id,@url,@type,@name,@name_cn,@summary,@eps,@eps_count,@air_date,@air_weekday,@score,@rank,@rate_total)";
                    conn.Execute(sqlCommandStr, subject);
                }
            }
        }

        internal void save(Subject subject)
        {
            using (IDbConnection conn = DapperService.MySqlConnection())
            {
                int id = subject.id;
                int count = conn.QuerySingleOrDefault<int>("SELECT COUNT(id) from `subject` WHERE id = " + id);
                if (count < 1)
                {
                    string sqlCommandStr = @"insert into subject(id,url,type,name,name_cn,summary,eps,eps_count,air_date,air_weekday,score,`rank`,rate_total) values(@id,@url,@type,@name,@name_cn,@summary,@eps,@eps_count,@air_date,@air_weekday,@score,@rank,@rate_total)";
                    conn.Execute(sqlCommandStr, subject);
                }
                else 
                {
                    string sqlCommandStr = @"UPDATE subject SET url=@url,type=@type,name=@name,name_cn=@name_cn,summary=@summary,eps=@eps,eps_count=@eps_count,air_date=@air_date,air_weekday=@air_weekday,score=@score,`rank`=@rank,rate_total=@rate_total where id=@id";
                    conn.Execute(sqlCommandStr, subject);
                }
            }
        }

        private int rate_totalStart = 0;

        internal void save(List<Subject> subjects)
        {
            if (subjects==null || subjects.Count < 1)
            {
                return;
            }
            using (IDbConnection conn = DapperService.MySqlConnection())
            {
                foreach (var subject in subjects)
                {
                    int id = subject.id;
                    int count = conn.QuerySingleOrDefault<int>("SELECT COUNT(id) from `subject` WHERE id = " + id);
                    if (count < 1)
                    {
                        string sqlCommandStr = @"insert into subject(id,url,type,name,name_cn,summary,eps,eps_count,air_date,air_weekday,score,`rank`,rate_total) values(@id,@url,@type,@name,@name_cn,@summary,@eps,@eps_count,@air_date,@air_weekday,@score,@rank,@rate_total)";
                        conn.Execute(sqlCommandStr, subject);
                    }
                    else
                    {
                        string sqlCommandStr = @"UPDATE subject SET url=@url,type=@type,name=@name,name_cn=@name_cn,summary=@summary,eps=@eps,eps_count=@eps_count,air_date=@air_date,air_weekday=@air_weekday,score=@score,`rank`=@rank,rate_total=@rate_total where id=@id";
                        conn.Execute(sqlCommandStr, subject);
                    }
                }                
            }
        }

        internal void setRate_totalStart(int rate_totalStart)
        {
            this.rate_totalStart = rate_totalStart;
        }
        internal int scoreCount(double start,double end)
        {
            using (IDbConnection conn = DapperService.MySqlConnection())
            {
                int count = conn.QuerySingleOrDefault<int>("SELECT COUNT(id) from `subject` WHERE  score >= @start AND score < @end AND  rate_total > @rate_totalStart", new { start = start,end = end, rate_totalStart= rate_totalStart });
                return count;
            }           
        }

        internal int rateTotalCount(int start, int end)
        {
            using (IDbConnection conn = DapperService.MySqlConnection())
            {
                int count = conn.QuerySingleOrDefault<int>("SELECT COUNT(id) from `subject` WHERE  rate_total >= @start AND rate_total < @end", new { start = start, end = end });
                return count;
            }
        }

        internal int timeCount(string start, string end)
        {
            using (IDbConnection conn = DapperService.MySqlConnection())
            {
                int count = conn.QuerySingleOrDefault<int>("SELECT COUNT(id) from `subject` WHERE  air_date >= @start AND air_date < @end AND  rate_total > @rate_totalStart", new { start = start, end = end, rate_totalStart = rate_totalStart });
                return count;
            }
        }
    }
}
